/**
 * Copyright (c) 2016. Visionet and/or its affiliates. All right reserved.
 * VISIONET PROPRIETARY/CONFIDENTIAL.
 */
package com.visionet.jumper.core.domain.bean;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.visionet.jumper.core.domain.search.*;
import com.visionet.jumper.core.domain.search.enumdata.JoinType;
import com.visionet.jumper.core.domain.search.filter.*;
import com.visionet.jumper.core.exception.runtime.BaseRuntimeException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.util.CollectionUtils;

import com.visionet.jumper.core.domain.search.enumdata.SearchOperator;

public class QueryBean {

	private String findSql;
	
	private String countSql;
	
	private List<Object> conditionList;
	
	private Integer num;
	
	public String getFindSql() {
		return findSql;
	}

	public void setFindSql(String findSql) {
		this.findSql = findSql;
	}
	
	public String getCountSql() {
		return countSql;
	}

	public void setCountSql(String countSql) {
		this.countSql = countSql;
	}

	public List<Object> getConditionList() {
		return conditionList;
	}

	public void setConditionList(List<Object> conditionList) {
		this.conditionList = conditionList;
	}

	public String findBySearchParams(Searchable searchable) {
		num = 0;
		conditionList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append(prepareTable(searchable));
		if (searchable.getLinkConditions() != null) {
			sql.append(prepareLinkConditions(searchable.getLinkConditions()));
		}
		if (!CollectionUtils.isEmpty(searchable.getSearchFilters())) {
			sql.append(prepareWhere(searchable.getSearchFilters()));
		}
		if (!CollectionUtils.isEmpty(searchable.getGroupByList())) {
			sql.append(prepareGroupBy(searchable.getGroupByList()));
		}
		if (!CollectionUtils.isEmpty(searchable.getHavingFilters())) {
			sql.append(prepareHaving(searchable.getHavingFilters()));
		}
		if (!CollectionUtils.isEmpty(searchable.getSearchSorts())) {
			sql.append(prepareOrderBy(searchable.getSearchSorts()));
		}
		findSql = prepareSelect(searchable) + sql.toString();
		countSql = getCountSql(sql.toString());
		return findSql;
	}
	
	private static String getCountSql(String sql) {
		StringBuilder countSql = new StringBuilder(SearchConstant.SELECT);
		countSql.append(SearchConstant.SPACE).append(SearchConstant.COUNT_ASTERISK).append(sql);
		int index = countSql.indexOf(SearchConstant.ORDER_BY);
		if (index != -1) {
			return countSql.substring(0, index);
		}
		return countSql.toString();
	}
	
	/**
	 * 拼接select
	 * @param searchable
	 * @return
	 */
	private String prepareSelect(Searchable searchable) {
		StringBuilder selectSql = new StringBuilder();
		List<String> selectList = searchable.getSelectList();
		if (!CollectionUtils.isEmpty(selectList)) {
			selectSql.append(SearchConstant.SELECT).append(SearchConstant.SPACE);
			if(searchable instanceof HqlSearchable && searchable.getTransfer() != null) {
				selectSql.append(SearchConstant.NEW).append(SearchConstant.SPACE)
						.append(searchable.getTransfer().getName()).append(SearchConstant.LEFT_BRACKET);
			}

			String[] fields;
			for (String string : selectList) {
				fields = StringUtils.split(string, SearchConstant.COMMA);
				for (String string1 : fields) {
					selectSql.append(string1).append(SearchConstant.COMMA);
				}
			}
			selectSql.deleteCharAt(selectSql.length() - 1);
			if(searchable instanceof HqlSearchable && searchable.getTransfer() != null) {
				selectSql.append(SearchConstant.RIGHT_BRACKET);
			}
		} else {
			if (searchable instanceof SqlSearchable) {
				selectSql.append(SearchConstant.SELECT).append(SearchConstant.SPACE).append(SearchConstant.ASTERISK);
			}
		}
		return selectSql.toString();
	}
	
	/**
	 * 拼接table
	 * @param searchable
	 * @return
	 */
	private String prepareTable(Searchable searchable) {
		StringBuilder tableSql = new StringBuilder(SearchConstant.SPACE);
		tableSql.append(SearchConstant.FROM).append(SearchConstant.SPACE);
		if (StringUtils.isEmpty(searchable.getTableName())) {
			throw new BaseRuntimeException("主表名为空");
		}
		tableSql.append(searchable.getTableName())
				.append(SearchConstant.SPACE)
				.append(searchable.getAliasName())
				.append(SearchConstant.COMMA);
		
		Map<String, String> aliasMap = searchable.getAliasMap();
		if (aliasMap != null) {
			for (Map.Entry<String, String> entry : aliasMap.entrySet()) {
				tableSql.append(entry.getKey())
						.append(SearchConstant.SPACE)
						.append(entry.getValue())
						.append(SearchConstant.COMMA);
			}
		}
		tableSql.deleteCharAt(tableSql.length() - 1);

		List<JoinTableCondition> joinTables = searchable.getJoinTable();
		if(!CollectionUtils.isEmpty(joinTables)) {
			for(JoinTableCondition joinTable : joinTables) {
				if(JoinType.LEFT_OUTER_JOIN.equals(joinTable.getJoinType())) {
					tableSql = this.combineJoinTable(tableSql, joinTable, JoinType.LEFT_OUTER_JOIN);
				} else if(JoinType.RIGHT_OUTER_JOIN.equals(joinTable.getJoinType())) {
					tableSql = this.combineJoinTable(tableSql, joinTable, JoinType.RIGHT_OUTER_JOIN);
				}
			}
		}
		tableSql.append(SearchConstant.SPACE)
				.append(SearchConstant.WHERE)
				.append(SearchConstant.SPACE)
				.append(SearchConstant.ONE_EQ_ONE);
		return tableSql.toString();
	}

	/**
	 * 拼接关联表查询
	 * @param tableSql
	 * @param joinTable
	 * @param joinType
	 * @return
	 */
	private StringBuilder combineJoinTable(StringBuilder tableSql, JoinTableCondition joinTable, JoinType joinType) {
		tableSql.append(SearchConstant.SPACE)
				.append(JoinType.valueOf(joinType.toString()).getName())
				.append(SearchConstant.SPACE)
				.append(joinTable.getJoinTableName())
				.append(SearchConstant.SPACE)
				.append(joinTable.getJoinAliasName());
		if(!CollectionUtils.isEmpty(joinTable.getJoinCondition())) {
			StringBuilder joinConditionSql = new StringBuilder();
			for(Map.Entry<String, String> entry : joinTable.getJoinCondition().entrySet()) {
				joinConditionSql.append(SearchConstant.SPACE)
						.append(SearchConstant.AND)
						.append(SearchConstant.SPACE)
						.append(entry.getKey())
						.append(SearchConstant.EQUAL)
						.append(entry.getValue());
			}
			tableSql.append(joinConditionSql.toString().replaceFirst(SearchConstant.AND, SearchConstant.ON));
		}
		return tableSql;
	}

	/**
	 * 拼接where条件
	 * @param list
	 * @return
	 */
	private String prepareWhere(List<SearchFilter> list) {
		StringBuilder whereSql = new StringBuilder();
		OrCondition orCondition;
		AndCondition andCondition;
		Condition condition;
		OrConditionGroup orConditionGroup;
		for (SearchFilter searchFilter : list) {
			if(searchFilter instanceof OrCondition) {
				orCondition = (OrCondition) searchFilter;
				whereSql.append(SearchConstant.SPACE).append(SearchConstant.OR).append(this.analysisCondition(orCondition.getCondition(), orCondition.getType(), orCondition.getObjValue()));
			} else if (searchFilter instanceof AndCondition) {
				andCondition = (AndCondition) searchFilter;
				whereSql.append(SearchConstant.SPACE).append(SearchConstant.AND).append(this.analysisCondition(andCondition.getCondition(), andCondition.getType(), andCondition.getObjValue()));
			} else if (searchFilter instanceof Condition) {
				condition = (Condition) searchFilter;
				whereSql.append(SearchConstant.SPACE).append(analysisCondition(condition.getCondition()));
				List<Object> objList = condition.getObjValue();
				if (!CollectionUtils.isEmpty(objList)) {
					for (Object obj : objList) {
						if (obj != null) {
							conditionList.add(obj);
						}
					}
				}
			} else if (searchFilter instanceof OrConditionGroup) {
				orConditionGroup = (OrConditionGroup) searchFilter;
				List<OrCondition> orConditions = orConditionGroup.getOrConditions();
				whereSql.append(SearchConstant.SPACE).append(SearchConstant.AND).append(SearchConstant.SPACE).append(SearchConstant.LEFT_BRACKET);
				OrCondition orCondition2;
				for (int i = 0; i < orConditions.size(); i++) {
					orCondition2 = orConditions.get(i);
					if (i != 0) {
						whereSql.append(SearchConstant.SPACE).append(SearchConstant.OR);
					}
					whereSql.append(this.analysisCondition(orCondition2.getCondition(), orCondition2.getType(), orCondition2.getObjValue()));
				}
				whereSql.append(SearchConstant.SPACE).append(SearchConstant.RIGHT_BRACKET);
			}
		}
		return whereSql.toString();
	}
	
	/**
	 * 拼接关联条件
	 * @param linkConditions
	 * @return
	 */
	private String prepareLinkConditions(Map<String, String> linkConditions) {
		StringBuilder linkSql = new StringBuilder();
		for (Map.Entry<String, String> entry : linkConditions.entrySet()) {
			linkSql.append(SearchConstant.SPACE).append(SearchConstant.AND).append(SearchConstant.SPACE).append(entry.getKey());
			linkSql.append(SearchConstant.SPACE).append(SearchConstant.EQUAL).append(SearchConstant.SPACE).append(entry.getValue());
			
		}
		return linkSql.toString();
	}
	
	/**
	 * 拼接groupBy
	 * @param groupByList
	 * @return
	 */
	private String prepareGroupBy(List<String> groupByList) {
		StringBuilder groupBySql = new StringBuilder(SearchConstant.SPACE);
		groupBySql.append(SearchConstant.GROUP_BY);
		String[] fields;
		for (String string : groupByList) {
			fields = StringUtils.split(string, SearchConstant.COMMA);
			for (String string1 : fields) {
				groupBySql.append(SearchConstant.SPACE).append(string1).append(SearchConstant.COMMA);
			}
		}
		groupBySql.deleteCharAt(groupBySql.length() - 1);
		return groupBySql.toString();
	}
	
	/**
	 * 拼接having子句
	 * @param list
	 * @return
	 */
	private String prepareHaving(List<SearchFilter> list) {
		int i = 0;
		StringBuilder havingSql = new StringBuilder(SearchConstant.SPACE);
		havingSql.append(SearchConstant.HAVING);
		OrCondition orCondition;
		AndCondition andCondition;
		Condition condition;
		for (SearchFilter searchFilter : list) {
			if(searchFilter instanceof OrCondition) {
				orCondition = (OrCondition) searchFilter;
				if (i == 0) {
					havingSql.append(this.analysisCondition(orCondition.getCondition(), orCondition.getType(), orCondition.getObjValue()));
				} else {
					havingSql.append(SearchConstant.SPACE).append(SearchConstant.OR).append(this.analysisCondition(orCondition.getCondition(), orCondition.getType(), orCondition.getObjValue()));
				}
			} else if (searchFilter instanceof AndCondition) {
				andCondition = (AndCondition) searchFilter;
				if (i == 0) {
					havingSql.append(this.analysisCondition(andCondition.getCondition(), andCondition.getType(), andCondition.getObjValue()));
				} else {
					havingSql.append(SearchConstant.SPACE).append(SearchConstant.AND).append(this.analysisCondition(andCondition.getCondition(), andCondition.getType(), andCondition.getObjValue()));
				}
			} else if (searchFilter instanceof Condition) {
				condition = (Condition) searchFilter;
				havingSql.append(SearchConstant.SPACE).append(analysisCondition(condition.getCondition()));
				List<Object> objList = condition.getObjValue();
				if (!CollectionUtils.isEmpty(objList)) {
					for (Object obj : objList) {
						if (obj != null) {
							conditionList.add(obj);
						}
					}
				}
			}
			i++;
		}
		return havingSql.toString();
	}
	
	/**
	 * 拼接orderBy
	 * @param searchSorts
	 * @return
	 */
	private String prepareOrderBy(List<SearchSort> searchSorts) {
		StringBuilder orderBySql = new StringBuilder(SearchConstant.SPACE);
		orderBySql.append(SearchConstant.ORDER_BY);
		for (SearchSort searchSort : searchSorts) {
			if (Direction.DESC.name().equals(searchSort.getType())) {
				orderBySql.append(SearchConstant.SPACE).append(searchSort.getField()).append(SearchConstant.SPACE).append(Direction.DESC.name()).append(SearchConstant.COMMA);
			} else if (Direction.ASC.name().equals(searchSort.getType())) {
				orderBySql.append(SearchConstant.SPACE).append(searchSort.getField()).append(SearchConstant.SPACE).append(Direction.ASC.name()).append(SearchConstant.COMMA);
			} else {
				throw new BaseRuntimeException("排序类型错误");
			}
			
		}
		orderBySql.deleteCharAt(orderBySql.length() - 1);
		return orderBySql.toString();
	}
	
	/**
	 * 解析自定义查询条件
	 * @param sql
	 * @return
	 */
	private String analysisCondition(String sql) {
		StringBuilder analysisSql = new StringBuilder();
		String[] tempConditions = StringUtils.split(sql, SearchConstant.QUESTION_MARK);
		int tempConditionsLength = tempConditions.length == 0? 0:tempConditions.length-1;
		for(int i=0; i<tempConditionsLength; i++) {
			num++;
			analysisSql.append(tempConditions[i]).append(SearchConstant.QUESTION_MARK).append(num);
		}
		analysisSql.append(tempConditions[tempConditionsLength]);
		return analysisSql.toString();
	}
	
	/**
	 * 解析查询条件
	 * @param condition
	 * @return
	 */
	private String analysisCondition(String condition, SearchOperator type, Object objValue) {
		objValue = this.formtValue(type.getKey(), objValue);
		if (objValue != null) {
			conditionList.add(objValue);
		}
		num++;
		StringBuilder analysisSql = new StringBuilder(SearchConstant.SPACE);
		analysisSql.append(condition).append(SearchConstant.SPACE).append(type.getValue()).append(SearchConstant.SPACE).append(SearchConstant.QUESTION_MARK).append(num);
		return analysisSql.toString();
	}
	
	private Object formtValue(String searchOperator, Object objValue) {
		if (SearchOperator.prefixLike.getKey().equals(searchOperator) || SearchOperator.prefixNotLike.getKey().equals(searchOperator)) {
			return objValue + SearchConstant.PERCENT_SIGN;
		} else if (SearchOperator.suffixLike.getKey().equals(searchOperator) || SearchOperator.suffixNotLike.getKey().equals(searchOperator)) {
			return SearchConstant.PERCENT_SIGN + objValue;
		} else if (SearchOperator.like.getKey().equals(searchOperator) || SearchOperator.notLike.getKey().equals(searchOperator)) {
			return SearchConstant.PERCENT_SIGN + objValue + SearchConstant.PERCENT_SIGN;
		}  else if (SearchOperator.isNull.getKey().equals(searchOperator) || SearchOperator.isNotNull.getKey().equals(searchOperator)) {
			return null;
		} else {
			return objValue;
		}
	}
}
